package cn.com.nes.site.module.dao.monitor;

import cn.com.nes.common.util.*;
import cn.com.nes.site.entity.DataReq;
import cn.com.nes.site.entity.PageData;
import cn.com.nes.site.module.dao.BaseDaoImpl;
import cn.hutool.core.util.StrUtil;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class FaultDaoImpl extends BaseDaoImpl {

    public PageData getFaultView(DataReq req){
        //行政区划下级分组
        StringBuffer sb = new StringBuffer();
        //无分组
        StringBuffer sb2 = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        StringBuffer sbfrom2 = new StringBuffer();
        PageData res = new PageData();
        Connection conn = null ;
        try {
            conn = DBUtils.getJdbcForOpe();
            String startTime = ((String) req.getReqData().get("startTime"));
            String endTime = ((String) req.getReqData().get("endTime"));

            String type = ((String) req.getReqData().get("type"));

            if (StrUtil.isEmpty(startTime)) {
                startTime = DBUitl.getDate(-1);
                endTime = DBUitl.getDate(0);
                startTime +=" 00:00:00";
                endTime +=" 23:59:59";
            }

            if ("1".equals(type) || "2".equals(type)) {  //点击饼图进入,只刷新表格
                return getFaultViewByPie(req);
            }

            //找下级行政区划
            String downXzqh = "concat(substr( iii.xzqh, 1, 2 ))";
            String xzqhjb = XzqhUtil.getXzqhjb(conn, req.getUinfo().getAreacode());
            if (!StrUtil.isEmpty(xzqhjb))
                downXzqh = XzqhUtil.getDownXzqh(req.getUinfo().getAreacode(), xzqhjb, "iii.xzqh");
            if(!StringUtils.isEmpty((String)req.getReqData().get("xzqhdm")) && !"86".equals((String) req.getReqData().get("xzqhdm"))) {
                String xzqhdm = (String) req.getReqData().get("xzqhdm");
                xzqhjb = XzqhUtil.getXzqhjb(conn, xzqhdm);
                downXzqh = XzqhUtil.getDownXzqh(xzqhdm, xzqhjb, "iii.xzqh");
            }

            //行政区划下级分组
            sb.append(" SELECT ");
            sb.append(" if(iefi.state ='0','已处理','待处理') as state,insert(iii.xmlxr , 2, 1, '**') xmlxr,concat(substr(iii.xmlxrdh,1,3),'****',substr(iii.xmlxrdh,8,4)) xmlxrdh,iei.sb_id,iei.alias, ");
            sb.append(" date_format(iefi.alert_time,'%Y-%m-%d %H:%i') gmt_create,iefi.erro_desc,iefi.erro_code,concat(iii.xmdz,ifnull(iii.mph,'')) address, ");
            if (!StrUtil.isEmpty(downXzqh)){
                sb.append(" "+downXzqh+" xzqhdm,");
            }else {
                sb.append(" concat(substr( iii.xzqh, 1, 4 ), '00000000' ) xzqhdm, ");
            }
            sb.append(" tx.xzqhmc ");
            sb.append(" from nes_ope_prod.is_equipment_info iei ");
            sb.append(" inner join is_equipment_fault_info iefi on iei.sb_id=iefi.sb_id  ");
            sb.append(" inner join t_sblx ts on iei.sblx_id = ts.sblx_id");
            sb.append(" left join nes_ope_prod.is_dict_value_info idvi on idvi.dic_no = ts.sblx and type_id = 'd_type_2' ");
            sb.append(" inner join nes_ope_prod.is_install_info iii on (iii.dd_id = iei.dd_id and iii.sfsc = 0) ");
            sb.append(" left join nes_ope_prod.t_xzqh tx on tx.xzqhdm ");
            if (!StrUtil.isEmpty(downXzqh)){
                sb.append(" = "+downXzqh+" ");
            }else {
                sb.append(" concat(substr( iii.xzqh, 1, 4 ), '00000000' ) xzqhdm ");
            }
            sb.append(" where iei.sfsc = 0 ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo().getAreacode(),"iii.xzqh"));
            if (!StrUtil.isEmpty(xzqhjb)) {
                if ("5".equals(xzqhjb)) {
                    sb.append(" and tx.xzqhjb = 5 ");
                } else {
                    sb.append(" and tx.xzqhjb > ").append(Integer.parseInt(xzqhjb));
                }
            }

            //无分组
            sb2.append(" SELECT ");
            sb2.append(" if(iefi.state ='0','已处理','待处理') as state,insert(iii.xmlxr , 2, 1, '**') xmlxr,concat(substr(iii.xmlxrdh,1,3),'****',substr(iii.xmlxrdh,8,4)) xmlxrdh,iei.sb_id,iei.alias, ");
            sb2.append(" date_format(iefi.alert_time,'%Y-%m-%d %H:%i') gmt_create,iefi.erro_desc,iefi.erro_code,concat(iii.xmdz,ifnull(iii.mph,'')) address, ");
            sb2.append(" tx.xzqhmc ");
            sb2.append(" from nes_ope_prod.is_equipment_info iei ");
            sb2.append(" inner join is_equipment_fault_info iefi on iei.sb_id=iefi.sb_id  ");
            sb2.append(" inner join t_sblx ts on iei.sblx_id = ts.sblx_id");
            sb2.append(" left join nes_ope_prod.is_dict_value_info idvi on idvi.dic_no = ts.sblx and type_id = 'd_type_2' ");
            sb2.append(" inner join nes_ope_prod.is_install_info iii on (iii.dd_id = iei.dd_id and iii.sfsc = 0) ");
            sb2.append(" left join nes_ope_prod.t_xzqh tx on tx.xzqhdm = iii.xzqh");
            sb2.append(" where iei.sfsc = 0 ");
            sb2.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo().getAreacode(),"iii.xzqh"));
            if (!StrUtil.isEmpty(xzqhjb)) {
                if ("5".equals(xzqhjb)) {
                    sb2.append(" and tx.xzqhjb = 5 ");
                } else {
                    sb2.append(" and tx.xzqhjb > ").append(Integer.parseInt(xzqhjb));
                }
            }

            //条件
            String companyType=req.getUinfo().getCompanyType();
            if (!Globals.DEMO_COMPANYID.equals(req.getUinfo().getCompanyId())) {//不是演示账号
                if ("01".equals(companyType)) {//厂商
                    sb.append(" and iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
                    sb2.append(" and iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
                } else if ("02".equals(companyType)) {//代理商
                    sb.append(" and iei.agent_id= '").append(req.getUinfo().getCompanyId()).append("' ");
                    sb2.append(" and iei.agent_id= '").append(req.getUinfo().getCompanyId()).append("' ");
                } else {//管理员
                    sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
                    sb2.append(" and iei.manufacture_id='MA3NU7QX1' ");
                }
            } else {//演示账号默认德尚
                sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
                sb2.append(" and iei.manufacture_id='MA3NU7QX1' ");
            }
//            if("01".equals(companyType)){//厂商
//                sb2.append(" and iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
//                sb.append(" and iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
//            }else if("02".equals(companyType)){//代理商
//                sb2.append(" and iei.agent_id= '").append(req.getUinfo().getCompanyId()).append("' ");
//                sb.append(" and iei.agent_id= '").append(req.getUinfo().getCompanyId()).append("' ");
//            }
            sb2.append(" AND iefi.alert_time >= '").append(startTime).append("' ");
            sb.append(" AND iefi.alert_time >= '").append(startTime).append("' ");
            sb2.append(" AND iefi.alert_time <= '").append(endTime).append("' ");
            sb.append(" AND iefi.alert_time <= '").append(endTime).append("' ");
            if (!StrUtil.isEmpty(((String) req.getReqData().get("sblx")))) {
                sb.append(" and idvi.value_id = '").append(req.getReqData().get("sblx")).append("'");
                sb2.append(" and idvi.value_id = '").append(req.getReqData().get("sblx")).append("'");
            }
            if (!StrUtil.isEmpty(((String) req.getReqData().get("sbxh")))) {
                sb.append(" and ts.sblx_id = '").append(req.getReqData().get("sbxh")).append("'");
                sb2.append(" and ts.sblx_id = '").append(req.getReqData().get("sbxh")).append("'");
            }
            if (!StrUtil.isEmpty(((String) req.getReqData().get("fault_name")))) {//故障描述模糊查询
                sb.append(" and iefi.erro_code like '%").append(req.getReqData().get("fault_name")).append("%'");
                sb2.append(" and iefi.erro_code like '%").append(req.getReqData().get("fault_name")).append("%'");
            }
            if (!StrUtil.isEmpty(((String) req.getReqData().get("erro_code")))) {//数据关联查询
                sb.append(" and iefi.erro_code =  '").append(req.getReqData().get("erro_code")).append("' ");
                sb2.append(" and iefi.erro_code =  '").append(req.getReqData().get("erro_code")).append("' ");
            }
            if (!StrUtil.isEmpty(req.getUinfo().getAreacode())){
                sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, (String) req.getUinfo().getAreacode(), "iii.xzqh"));
                sb2.append(XzqhUtil.getMultipleQueryXzqhConn(conn, (String) req.getUinfo().getAreacode(), "iii.xzqh"));
            }
            if(!StringUtils.isEmpty((String)req.getReqData().get("xzqhdm")) && !"86".equals((String) req.getReqData().get("xzqhdm"))) {
                sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, (String) req.getReqData().get("xzqhdm"), "iii.xzqh"));
                sb2.append(XzqhUtil.getMultipleQueryXzqhConn(conn, (String) req.getReqData().get("xzqhdm"), "iii.xzqh"));
            }
            if (!StrUtil.isEmpty(((String) req.getReqData().get("sb_id")))) {
                sb.append(" and iei.sb_id =  '").append(req.getReqData().get("sb_id")).append("' ");
                sb2.append(" and iei.sb_id =  '").append(req.getReqData().get("sb_id")).append("' ");
            }

            sb = DBUitl.sql_check(sb);
            sb2 = DBUitl.sql_check(sb2);

            // 分页
            sbfrom.append(sb2.toString());
            int idx = sbfrom.indexOf(" from ");
            sbfrom.delete(0, idx);
            Page page1 = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, null);
            sb2.append(" ORDER BY iefi.erro_desc,gmt_create DESC ");
            sb2.append(" limit ").append(page1.getOffsetlimit()).append(",").append(req.getPagesize());

            List<Map<String, Object>> maps = this.queryForList(conn, sb2.toString(), new Object[]{});
            res.setPage(maps);

            //饼图
            HashMap<String, Object> extraData = new HashMap<String, Object>();
            sb2.delete(0, sb2.length());
            sb2.append("SELECT iefi.erro_code,count(1) cnt ");
            sb2.append(sbfrom);
            sb2.append(" GROUP BY iefi.erro_code ");
            sb2.append(" ORDER BY cnt desc limit 6 ");
            List<Map<String, Object>> maps1 = this.queryForList(conn, sb2.toString(), new Object[]{});
            extraData.put("pie", maps1);

            //柱状图
            sbfrom2.append(sb.toString());
            int idx2 = sbfrom2.indexOf(" from ");
            sbfrom2.delete(0, idx2);
            sb.delete(0, sb.length());
            sb.append("SELECT tx.xzqhdm, tx.xzqhmc,count(1) cnt ");
            sb.append(sbfrom2);
            if (!StrUtil.isEmpty(downXzqh)){
                sb.append(" GROUP BY "+downXzqh+" ");
            }else {
                sb.append(" GROUP BY concat(substr( iii.xzqh, 1, 4 ), '00000000' ) ");
            }
            sb.append(" ORDER BY cnt desc ");
            List<Map<String, Object>> maps2 = this.queryForList(conn, sb.toString(), new Object[]{});
            extraData.put("bar", maps2);

            //返回图形结果
            res.setExtraData(extraData);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn!=null){
                try {
                    conn.close() ;
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return res;
    }

    public PageData getFaultViewByPie(DataReq req) {
        StringBuffer sb = new StringBuffer();
        PageData res = new PageData();
        StringBuffer sbfrom = new StringBuffer();
        Connection conn = null ;
        try {
            conn = DBUtils.getJdbcForOpe();
            String startTime = ((String) req.getReqData().get("startTime"));
            String endTime = ((String) req.getReqData().get("endTime"));
            if (StrUtil.isEmpty(startTime)) {
                startTime = DBUitl.getDate(-1);
                endTime = DBUitl.getDate(0);
                startTime +=" 00:00:00";
                endTime +=" 23:59:59";
            }

            //根据账户获取行政区划级别
            String xzqhjb = XzqhUtil.getXzqhjb(conn, req.getUinfo().getAreacode());
            if(!StringUtils.isEmpty((String)req.getReqData().get("xzqhdm")) && !"86".equals((String) req.getReqData().get("xzqhdm"))) {
                String xzqhdm = (String) req.getReqData().get("xzqhdm");
                xzqhjb = XzqhUtil.getXzqhjb(conn, xzqhdm);
            }

            //查询
            sb.append(" SELECT ");
            sb.append(" if(iefi.state ='0','已处理','待处理') as state,insert(iii.xmlxr , 2, 1, '**') xmlxr,concat(substr(iii.xmlxrdh,1,3),'****',substr(iii.xmlxrdh,8,4)) xmlxrdh,iei.sb_id,iei.alias, ");
            sb.append(" iefi.erro_desc,iefi.erro_code,date_format( iefi.alert_time, '%m-%d %H:%i' ) gmt_create, ");
            sb.append(" tx.xzqhmc");
            sb.append(" from nes_ope_prod.is_equipment_info iei ");
            sb.append(" inner join is_equipment_fault_info iefi on iei.sb_id=iefi.sb_id  ");
            sb.append(" inner join t_sblx ts on iei.sblx_id = ts.sblx_id");
            sb.append(" left join nes_ope_prod.is_dict_value_info idvi on idvi.dic_no = ts.sblx and type_id = 'd_type_2' ");
            sb.append(" inner join nes_ope_prod.is_install_info iii on (iii.dd_id = iei.dd_id and iii.sfsc = 0) ");
            sb.append(" left join nes_ope_prod.t_xzqh tx on tx.xzqhdm = iii.xzqh ");
            sb.append(" where iei.sfsc = 0 ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo().getAreacode(),"iii.xzqh"));
            String companyType=req.getUinfo().getCompanyType();
            //条件
            if("01".equals(companyType)){//厂商
                sb.append(" and iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
            }else if("02".equals(companyType)){//代理商
                sb.append(" and iei.agent_id= '").append(req.getUinfo().getCompanyId()).append("' ");
            }
            sb.append(" AND iefi.alert_time >= '").append(startTime).append("' ");
            sb.append(" AND iefi.alert_time <= '").append(endTime).append("' ");
            if (!StrUtil.isEmpty(((String) req.getReqData().get("sblx"))))
                sb.append(" and idvi.value_id = '").append(req.getReqData().get("sblx")).append("'");
            if (!StrUtil.isEmpty(((String) req.getReqData().get("sbxh"))))
                sb.append(" and ts.sblx_id = '").append(req.getReqData().get("sbxh")).append("'");
            if (!StrUtil.isEmpty(((String) req.getReqData().get("erro_code"))))
                sb.append(" and iefi.erro_code like '%").append(req.getReqData().get("erro_code")).append("%'");
            if (!StrUtil.isEmpty(req.getUinfo().getAreacode()))
                sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, (String) req.getUinfo().getAreacode(), "iii.xzqh"));
            if(!StringUtils.isEmpty((String)req.getReqData().get("xzqhdm")) && !"86".equals((String) req.getReqData().get("xzqhdm")))
                sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, (String) req.getReqData().get("xzqhdm"),"iii.xzqh"));
            if (!StrUtil.isEmpty(xzqhjb)) {
                if ("5".equals(xzqhjb)) {
                    sb.append(" and tx.xzqhjb = 5 ");
                } else {
                    sb.append(" and tx.xzqhjb > ").append(Integer.parseInt(xzqhjb));
                }
            }
            sb = DBUitl.sql_check(sb);

            // 分页
            sbfrom.append(sb.toString());
            Page page1 = new Page(conn, " from ("+sbfrom.toString()+" ) tab", req.getPageid(), req.getPagesize(), res, null);
            sb.append(" limit ").append(page1.getOffsetlimit()).append(",").append(req.getPagesize());
            List<Map<String, Object>> page = this.queryForList(conn, sb.toString(), new Object[]{});
            //返回结果
            res.setPage(page);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return res;
    }

    public PageData getFaultList(DataReq req){
        PageData res = new PageData();
        HashMap<String, Object> param = req.getReqData();
        StringBuffer sb = new StringBuffer();
        Connection conn = null ;
        try {
            conn = DBUtils.getJdbcForOpe();
            String sortedBy = (String) param.get("sort");
            String orders = (String) param.get("sortOrder");
            StringBuffer orderBy = new StringBuffer(" ORDER BY ");

            if (StringUtils.isEmpty(sortedBy)) {
                orderBy.append(" gmt_create desc  ");
            }else {
                //排序字段
                if ("no".equals(sortedBy))
                    orderBy.append(" gmt_create desc  ");
                else orderBy.append(String.format("%s %s", sortedBy, orders.equals("ascending")?"asc":"desc"));
            }

            //故障信息
            sb.delete(0, sb.length());
            sb.append("SELECT DISTINCT '主机' type,insert(iii.xmlxr , 2, 1, '**') xmlxr ,concat(substr(iii.xmlxrdh,1,3),'****',substr(iii.xmlxrdh,8,4)) xmlxrdh,");
            sb.append(" ind.dtu_id as dtuId,");
            sb.append(" iei.sb_id,ifnull(iei.alias,'') as alias,ind.fault_codes,date_format( ind.gmt_create, '%Y-%m-%d %T' ) gmt_create,concat(iii.xmdz,ifnull(iii.mph,'')) address ");
            sb.append(" from nes_ope_prod.is_equipment_info iei ");
            sb.append(" inner join nes_ope_prod.t_sblx ts on iei.sblx_id = ts.sblx_id ");
            sb.append(" left join nes_ope_prod.is_dict_value_info v on v.dic_no = ts.sblx and type_id = 'd_type_2' ");
            sb.append(" INNER JOIN nes_ope_prod.is_install_info iii ON (iii.dd_id = iei.dd_id and iii.sfsc = 0) ");
            sb.append(" INNER JOIN nes_ope_prod.is_norm_data ind ON iei.sb_id = ind.sb_id AND ind.fault_codes IS NOT NULL ");
            sb.append(" where iei.sfsc=0 and ind.fault_codes<>'0' and ind.fault_codes<>'0,0'");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo().getAreacode(),"iii.xzqh"));

            String companyType = req.getUinfo().getCompanyType();
            if ("01".equals(companyType)) {//厂商
                sb.append(" and iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
            } else if ("02".equals(companyType)) {//代理商
                sb.append(" and iei.agent_id= '").append(req.getUinfo().getCompanyId()).append("' ");
            }

            if(!StringUtils.isEmpty((String)param.get("xzqhdm")) && !"86".equals((String) param.get("xzqhdm"))) {
                sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, (String) param.get("xzqhdm"),"iii.xzqh"));
            }

            if (!StringUtils.isEmpty((String) param.get("sblx"))) {
                sb.append(" and v.value_id = '").append((String) param.get("sblx")).append("' ");
            }

            if (!StringUtils.isEmpty((String) param.get("sbxh"))) {
                sb.append(" and ts.sblx_id = '").append((String) param.get("sbxh")).append("' ");
            }

            //故障描述
            if (!StringUtils.isEmpty((String) param.get("erro_code"))) {
                sb.append(" and ind.fault_codes like  '%").append(req.getReqData().get("erro_code")).append("%' ");
            }

            //创建时间
            if (!StringUtils.isEmpty((String) param.get("startTime"))) {
                sb.append(" and ind.gmt_create>=  '").append(req.getReqData().get("startTime")).append("' ");
            }

            if (!StringUtils.isEmpty((String) param.get("endTime"))) {
                sb.append(" and ind.gmt_create<=  '").append(req.getReqData().get("endTime")).append("' ");
            }

            sb.append(" and ind.gmt_create>DATE_SUB(CURDATE(), INTERVAL 100 DAY)   ");
            sb = DBUitl.sql_check(sb);

            // 分页
            Page page1 = new Page(conn, " from ( " + sb.toString() + " ) t_all", req.getPageid(), req.getPagesize(), res, null);
            sb.append(orderBy);
            sb.append(" limit ").append(page1.getOffsetlimit()).append(",").append(req.getPagesize());
            List<Map<String, Object>> list = this.queryForList(conn,sb.toString(), new Object[]{});
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(conn!=null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
        return res;
    }

    public PageData getFaultListDetails(DataReq req){
        PageData res = new PageData();
        HashMap<String, Object> param = req.getReqData();
        StringBuffer sb = new StringBuffer();
        Connection conn_click = null ;
        try {
            conn_click = DBUtils.getJNDIclikchouse() ;
            //故障信息
            String startTime = (String) param.get("startTime");
            String endTime = (String) param.get("endTime");
            String sbId = (String) param.get("sbId");
            if (StringUtils.isEmpty(startTime)) {
                startTime = DBUitl.getDate(-30) + " 00:00:00";
            }
            if (StringUtils.isEmpty(endTime)){
                endTime = DBUitl.getDate(0) + " 23:59:59";
            }
            //故障历史信息
            sb.delete(0, sb.length());
            sb.append("SELECT formatDateTime(gmt_create,'%F %T') gmtCreate,fault_code,if (fault_code is null or fault_code='',0,1) val FROM is_norm_data_his_buffer where sb_id='").append(sbId).append("'  ");
            sb.append(" and gmt_create>=toDateTime('").append(startTime).append("') and gmt_create<=toDateTime('").append(endTime).append("') order by gmt_create   ");
            sb = DBUitl.sql_check(sb);

            List<Map<String, Object>> list = this.queryForList(conn_click,sb.toString(), new Object[]{});
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(conn_click!=null)
                try {
                    conn_click.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
        return res;
    }

}
